package com.hsnn.medstgmini.base.dict.dao.impl;

import java.io.OutputStream;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.dict.dao.DicSysDao;
import com.hsnn.medstgmini.base.dict.model.DicSys;
import com.hsnn.medstgmini.base.dict.model.DicSysDicLog;
import com.hsnn.medstgmini.dao.impl.HibernateSqlUtilImpl;
import com.hsnn.medstgmini.exception.MSException;
import com.hsnn.medstgmini.util.Pagination;
/**
 * @category 自定义字典
 * @author zhangj
 */
@Repository
public class DicSysDaoImpl extends BaseHibernateDAO implements DicSysDao {
	
	@Autowired
	private HibernateSqlUtilImpl dao;
	
	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;
	
	/**
	 * @category 自定义字典
	 * @param page
	 * @param dicSys
	 * @return 自定义字典列表
	 */
	@Override
	public Pagination getDicSysList(Pagination page) {
		Map<String,String> params = new HashMap<String, String>();
		String classifyCode = page.getConditions().get("classifyCode");
		String classifyName = page.getConditions().get("classifyName");
		String dicCode = page.getConditions().get("dicCode");
		String dicName = page.getConditions().get("dicName");
		String status = page.getConditions().get("status");
		
		StringBuffer  sql = new StringBuffer();
		sql.append("select s.sys_dic_id sysDicId ,s.classify_code classifyCode,s.classify_name classifyName, ");
		sql.append(" s.dic_code dicCode,s.dic_name dicName,s.remark remark, ");
		sql.append(" u.name updUser,DATE_FORMAT(s.upd_datetime,'%Y-%m-%d %H:%i:%S') updDatetime, s.status status ");
		sql.append(" from dic_sysdic s left join sys_user u on s.upd_user=u.id where s.status = 1 and  1=1 ");
		
		if(StringUtils.isNotBlank(classifyCode)){
			sql.append(" and s.classify_code = :classifyCode ");
			params.put("classifyCode", classifyCode.trim());
		}
		if(StringUtils.isNotBlank(classifyName)){
			sql.append(" and s.classify_name = :classifyName ");
			params.put("classifyName", classifyName.trim());
		}
		if(StringUtils.isNotBlank(dicCode)){
			sql.append(" and s.dic_code = :dicCode ");
			params.put("dicCode", dicCode.trim());
		}
		if(StringUtils.isNotBlank(dicName)){
			sql.append(" and s.dic_name = :dicName ");
			params.put("dicName", dicName.trim());
		}
		if(StringUtils.isNotBlank(status)){
			sql.append(" and s.status = :status ");
			params.put("status", status.trim());
		}
		
		sql.append(" order by s.classify_code asc,cast(s.dic_code as unsigned) asc ");
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	
	
	
	

	
	/**
	 * @category 启用|停用
	 * @param dicPhar
	 * @return
	 */
	public int changeStatus(DicSys dicSys) {
		if (dicSys != null && dicSys.getSysDicId() != null) {
			Session session = sessionFactory.getCurrentSession();
			StringBuffer sb = new StringBuffer();
			sb.append("update DicSys set ");
			sb.append(" status = :status ");
			sb.append(", updUser = :updUser ");
			sb.append(", updDatetime = :updDatetime ");
			sb.append(" where sysDicId = :sysDicId and status <> :status");

			Query query = session.createQuery(sb.toString());
			query.setShort("status", dicSys.getStatus());
			query.setString("updUser", dicSys.getUpdUser());
			query.setTimestamp("updDatetime", new Date());
			query.setString("sysDicId", dicSys.getSysDicId());
			int count = query.executeUpdate();
			session.flush();
			if(count>0){
				writeLog(dicSys.getSysDicId());
			}
			return count;
		}
		return 0;
	}

	/**
	 * @category 批量(启用|停用)
	 * @param array
	 * @return
	 */
	public int batchChangeStatus(List<DicSys> list) {
		int sum = 0;
		if (list != null && list.size() > 0) {
			for (DicSys dicSys : list) {
				int count = changeStatus(dicSys);
				sum += count;
			}
		}
		return sum;
	}

	/**
	 * @category 根据id查询自定义字典
	 * @param id
	 * @return
	 */
	public DicSys getDicSysById(String id) {
		if (id != null && !"".equals(id)) {
			Session session = sessionFactory.getCurrentSession();
			Criteria criteria = session.createCriteria(DicSys.class);
			criteria.add(Restrictions.eq("sysDicId", id));
			return (DicSys) criteria.uniqueResult();
		}
		return null;
	}

	public void checkSameClassifyCode(String classifyCode, int opType) {
		if (classifyCode != null && !"".equals(classifyCode)) {
			Session session = sessionFactory.getCurrentSession();
			String hql = "select count(*) from DicSys where classifyCode = :classifyCode";
			Query query = session.createQuery(hql);
			query.setString("classifyCode", classifyCode);
			Long count = (Long) query.uniqueResult();
			if ((opType == 0 && count > 0) || (opType == 1 && count > 1)) {
				throw new MSException("自定义字典分类编码已经存在，请重新输入！");
			}
		}
	}

	
	
	/**
	 * @category 查询自定义字典维护记录列表
	 * @param page
	 * @param dicSysDicLog
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<DicSysDicLog> getDicSysDicLogList(Pagination page, DicSysDicLog dicSysDicLog) {
		Session session = sessionFactory.getCurrentSession();
		// 查询总记录数
		StringBuffer sb = new StringBuffer();
		sb.append("select count(*) from DicSysDicLog s1, DicSys s2 ");
		sb.append("where s1.sysDicId = s2.sysDicId and s1.sysDicId = :sysDicId ");

		if (dicSysDicLog != null) {
			Date start = dicSysDicLog.getStartTime();
			if (start != null) {
				sb.append(" and s1.updDatetime >= :startTime ");
			}
			Date end = dicSysDicLog.getEndTime();
			if (end != null) {
				sb.append(" and s1.updDatetime <= :endTime ");
			}
		}

		Query query = session.createQuery(sb.toString());

		query.setString("sysDicId", dicSysDicLog.getSysDicId());

		if (dicSysDicLog != null) {
			Date start = dicSysDicLog.getStartTime();
			if (start != null) {
				query.setTimestamp("startTime", start);
			}
			Date end = dicSysDicLog.getEndTime();
			if (end != null) {
				query.setTimestamp("endTime", end);
			}
		}

		Long total = (Long) query.uniqueResult();
		page.setRecords(total);

		// 查询分页数据
		sb.setLength(0);
		sb.append("select s1.classify_code,s1.classify_name, ");
		sb.append(" s1.dic_code,s1.dic_name,s1.remark, ");
		sb.append(" s3.name upd_user,(DATE_FORMAT(s1.upd_datetime,'%Y-%m-%d %H:%i:%S'))upd_datetime, s1.status,s1.log_id ");
		sb.append(" from dic_sysdic_log s1 ");
		sb.append(" inner join dic_sysdic s2 on s1.sys_dic_id=s2.sys_dic_id ");
		sb.append(" left join sys_user s3 on s1.upd_user=s3.id where s1.sys_dic_id = :sysDicId ");
		if (dicSysDicLog != null) {
			Date start = dicSysDicLog.getStartTime();
			if (start != null) {
				sb.append(" and s1.upd_datetime >= :startTime ");
			}
			Date end = dicSysDicLog.getEndTime();
			if (end != null) {
				sb.append(" and s1.upd_datetime <= :endTime ");
			}
		}

		sb.append(" order by s1.upd_datetime desc");

		query = session.createSQLQuery(sb.toString());

		query.setString("sysDicId", dicSysDicLog.getSysDicId());

		if (dicSysDicLog != null) {
			Date start = dicSysDicLog.getStartTime();
			if (start != null) {
				query.setTimestamp("startTime", start);
			}
			Date end = dicSysDicLog.getEndTime();
			if (end != null) {
				query.setTimestamp("endTime", end);
			}
		}

		query.setFirstResult(page.getFirstResult());
		query.setMaxResults(page.getMaxResults());
		return query.list();
	}
	/**
	 * @category 自定义字典类型
	 * @author 张俊
	 * @date   2015-06-11
	 * @param 
	 * @return DicSys
	 */
	@SuppressWarnings("unchecked")
	public List<DicSys> getDicSysByClassName() {
		String hql = "select distinct s.classifyCode, s.classifyName from DicSys s ";
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createQuery(hql);
		return query.list();
	}
	

	@SuppressWarnings("unchecked")
	public List<DicSys> getAll() {
		Query query = sessionFactory.getCurrentSession().createQuery("from DicSys");
		return query.list();
	}
	@SuppressWarnings("unchecked")
	public List<DicSys> getDicSysListByPropertyName(String propertyName,Object value ) {

		try {
			String queryString = "from DicSys as model where model."
					+ propertyName + "= ?";
			Query queryObject = sessionFactory.getCurrentSession().createQuery(queryString);
			queryObject.setParameter(0, value);
			return queryObject.list();
		} catch (RuntimeException re) {
			throw re;
		}
	}

	
	/**
	 * 基础库 系统字典  维护日志
	 * @param result
	 * @return
	 */
	@Override
	public Pagination getSysDicLogList(Pagination page) {
		String sysDicId = page.getConditions().get("sysDicId");
		String startTime = page.getConditions().get("startTime");
		String endTime = page.getConditions().get("endTime");
		StringBuffer  sql = new StringBuffer();
		
		sql.append("select s1.detail,s1.classify_code classifyCode,s1.classify_name classifyName, ")
		.append(" s1.dic_code dicCode,s1.dic_name dicName,s1.remark remark, ")
		.append(" s3.name updUser,DATE_FORMAT(s1.upd_datetime,'%Y-%m-%d %H:%i:%S')updDatetime, s1.status status,s1.log_id logId ")
		.append(" from dic_sysdic_log s1 ")
		.append(" left join dic_sysdic s2 on s1.sys_dic_id=s2.sys_dic_id ")
		.append(" left join sys_user s3 on s1.upd_user=s3.id where s1.sys_dic_id = :sysDicId ");
		Map<String,String> params = new HashMap<String, String>();
		params.put("sysDicId", sysDicId);
		if(StringUtils.isNotBlank(startTime)){
			sql.append(" and DATE_FORMAT(s1.upd_datetime,'%Y-%m-%d %H:%i:%S') >= :startTime ");
			params.put("startTime", startTime.trim()+" 00:00:00");
		}
		if(StringUtils.isNotBlank(endTime)){
			sql.append(" and DATE_FORMAT(s1.upd_datetime,'%Y-%m-%d %H:%i:%S') <= :endTime ");
			params.put("endTime", endTime.trim()+" 23:23:59");
		}
		sql.append(" order by s1.upd_datetime desc ");
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	
	
	
	/**
	 * 基础库 系统在字典  日志详情
	 * @param result
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, Object>> getDicSysDicLogListDetail(Pagination page) {
		Session session = sessionFactory.getCurrentSession();
		String logId = page.getConditions().get("logId");
		StringBuffer  sql = new StringBuffer();
		sql.append(" SELECT a.classify_code classifyCode,a.classify_name classifyName, a.dic_code dicCode,a.dic_name dicName,a.remark remark, c.name updUser, ")
		   .append(" DATE_FORMAT(a.upd_datetime,'%Y-%m-%d %H:%i:%S')updDatetime, a.status status FROM dic_sysdic_log a ")
		   .append(" LEFT JOIN dic_sysdic b on a.sys_dic_id=b.sys_dic_id LEFT JOIN sys_user c on a.upd_user=c.id  ")
		   .append(" WHERE a.sys_dic_id = (SELECT sys_dic_id FROM dic_sysdic_log WHERE log_id = :logId)  ")
		   .append(" AND a.upd_datetime <= (SELECT upd_datetime FROM dic_sysdic_log WHERE log_id = :logId)  ")
		   .append(" ORDER BY a.upd_datetime DESC LIMIT 2 ");
		Query query = session.createSQLQuery(sql.toString());
		query.setString("logId", logId);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		return query.list();
	}




	/**
	 * 自定义字典  新增和编辑
	 * @param dicSys
	 */
	@SuppressWarnings("unchecked")
	@Override
	public void addAndEdit(Pagination page) {
		Session session = sessionFactory.getCurrentSession();
		DicSys dicSys = (DicSys) page.getForm();
		if(StringUtils.isNotBlank(dicSys.getSysDicId())){
			
			StringBuffer sb = new StringBuffer()//如果修改了字典分类编码和名称，那么同名的分类也要改变
			.append("select c.sys_dic_id from dic_sysdic c WHERE c.classify_code = (SELECT  classify_code FROM dic_sysdic WHERE sys_dic_id = :sysDicId ")
			.append(" and  EXISTS (SELECT  classify_code FROM dic_sysdic WHERE sys_dic_id = :sysDicId AND (classify_code <> :classifyCode OR classify_name <> :classifyName)))")
			.append(" and c.sys_dic_id <> :sysDicId ");
			Query query3 = session.createSQLQuery(sb.toString());
			query3.setString("sysDicId", dicSys.getSysDicId());
			query3.setString("classifyCode", dicSys.getClassifyCode());
			query3.setString("classifyName", dicSys.getClassifyName());
			List<String> list= query3.list();
			if(list != null && list.size() > 0){
				StringBuffer sql2 = new StringBuffer();
				sql2.append("update dic_sysdic set  classify_code = :classifyCode, classify_name = :classifyName, ")
				   .append(" upd_datetime = :updDatetime, upd_user = :updUser where sys_dic_id in (:sysDicId) ");
				Query query2 = session.createSQLQuery(sql2.toString());
				query2.setString("classifyCode", dicSys.getClassifyCode());
				query2.setString("classifyName", dicSys.getClassifyName());
				query2.setString("updUser", dicSys.getUpdUser());
				query2.setParameterList("sysDicId", list);
				query2.setParameter("updDatetime", dicSys.getUpdDatetime());
				query2.executeUpdate();
				session.flush();
				for(int i = 0;i<list.size();i++){
					writeLog(list.get(i));
				}
			}
			
			//update
			StringBuffer sql = new StringBuffer();
			sql.append("update dic_sysdic set  classify_code = :classifyCode, classify_name = :classifyName,dic_code = :dicCode, ")
			   .append(" dic_name = :dicName,upd_datetime = :updDatetime, upd_user = :updUser,remark = :remark where sys_dic_id = :sysDicId");
			Query query = session.createSQLQuery(sql.toString());
			query.setString("classifyCode", dicSys.getClassifyCode());
			query.setString("classifyName", dicSys.getClassifyName());
			query.setString("dicCode", dicSys.getDicCode());
			query.setString("dicName", dicSys.getDicName());
			query.setString("updUser", dicSys.getUpdUser());
			query.setString("remark", dicSys.getRemark());
			query.setString("sysDicId", dicSys.getSysDicId());
			query.setParameter("updDatetime", dicSys.getUpdDatetime());
			query.executeUpdate();
			session.flush();
			writeLog(dicSys.getSysDicId());
		}else{//add
			String uuid = UUID.randomUUID().toString();
			StringBuffer sql = new StringBuffer();
			sql.append(" insert into dic_sysdic (sys_dic_id,classify_code,classify_name,dic_code,dic_name,status,upd_datetime,upd_user,remark) values ")
			   .append("  (:uuid,:classifyCode,:classifyName,:dicCode,:dicName, ")
			   .append(" 1, :updDatetime,:updUser,:remark ) ");
			Query query = session.createSQLQuery(sql.toString());
			query.setString("uuid", uuid);
			query.setString("classifyCode", dicSys.getClassifyCode());
			query.setString("classifyName", dicSys.getClassifyName());
			query.setString("dicCode", dicSys.getDicCode());
			query.setString("dicName", dicSys.getDicName());
			query.setString("updUser", dicSys.getUpdUser());
			query.setString("remark", dicSys.getRemark());
			query.setParameter("updDatetime", dicSys.getUpdDatetime());
			query.executeUpdate();
			session.flush();
			writeLog(uuid);
		}
	}




	/**
	 * 
	 *@category 写日志（新增、编辑、启用、停用 操作后将操作记录写入日志维护记录）
	 *@author wangbing
	 *@date 2015年6月9日
	 *Pagination
	 */
	@Override
	public void writeLog(String sysDicId) {
		Session session = getSession();
		StringBuffer detail = new StringBuffer();
		
		StringBuffer sb = new StringBuffer()
		.append(" select classify_code,classify_name,dic_code,dic_name,remark,status ")
		.append(" from dic_sysdic where sys_dic_id = :sysDicId ")
		.append(" union all ")
		.append(" select t.* from (select classify_code,classify_name,dic_code,dic_name,remark,status ")
		.append(" from dic_sysdic_log where sys_dic_id = :sysDicId  order by upd_datetime desc limit 1 ) t");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("sysDicId", sysDicId);
		List<?> list = dao.getSqlList(sb.toString(), params);
		
		String[] colunm ={"字典分类编码","字典分类名称","字典编码","字典内容","备注","状态"};
		if(list.size()==1){
			Object[] obj =(Object[]) list.get(0);
			obj[5] = obj[5]==null?"":"1".equals(obj[5].toString())?"启用":"停用";
			for(int i = 0;i<obj.length;i++){
				detail.append(";").append(colunm[i]).append(":").append(obj[i]==null?"":obj[i].toString());
			}
			detail.delete(0, 1);
		}else{
			Object[] obj1 =(Object[]) list.get(0);//修改后
			Object[] obj2 =(Object[]) list.get(1);//修改前
			obj1[5] = obj1[5]==null?"":"1".equals(obj1[5].toString())?"启用":"停用";
			obj2[5] = obj2[5]==null?"":"1".equals(obj2[5].toString())?"启用":"停用";
			for(int i = 0;i<obj1.length;i++){
				if(!(obj1[i]==null?"":obj1[i].toString()).equals(obj2[i]==null?"":obj2[i].toString())){
					detail.append(";").append(colunm[i]).append(":").append(obj2[i]==null?"":obj2[i].toString()).append(",").append(obj1[i]==null?"":obj1[i].toString());
				}
				
			}
			detail.delete(0, 1);
		}
		StringBuffer sql = new StringBuffer();
		sql.append("insert into dic_sysdic_log select UUID(),t.*,:detail from dic_sysdic t where t.sys_dic_id= :sysDicId ");
		Query query=session.createSQLQuery(sql.toString());
		query.setString("sysDicId", sysDicId);
		query.setString("detail", detail.toString());
		query.executeUpdate();
		session.flush();
		
		
	}
	
	/**
	 * 根据code或name查看有无重复数据
	 * @param code,name
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<DicSys> getDicSysBy(String key,String value,String id) {
		StringBuffer hql = new StringBuffer();
		hql.append("from DicSys where ")
		   .append(key)
		   .append(" = ? and sysDicId <> ?");
		Query query = getSession().createQuery(hql.toString());
		query.setParameter(0, value);
		query.setParameter(1,id);
		return query.list();
	}
	
	
	
	/**
	 * @category 根据条件查询系统字典
	 * @author zhouqi
	 * @date 2015年9月25日14:52:51
	 * @param conditions
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<DicSys> getSysDicByCondition(Map<String,String> conditions){
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from dic_sysdic A where 1=1");
		if(StringUtils.isNotBlank(conditions.get("classifyCode"))){
			sql.append(" AND A.classify_code = :classifyCode");
		}
		if(StringUtils.isNotBlank(conditions.get("classifyCode"))){
			sql.append(" AND A.classify_name = :classifyName");
		}
		Query query = session.createSQLQuery(sql.toString()).addEntity(DicSys.class);
		if(StringUtils.isNotBlank(conditions.get("classifyCode"))){
			query.setParameter("classifyCode", conditions.get("classifyCode"));
		}
		if(StringUtils.isNotBlank(conditions.get("classifyName"))){
			query.setParameter("classifyName", conditions.get("classifyName"));
		}
		return query.list();
	}
	
	@Override
	public void exportExcel(Pagination page,OutputStream os){
        HSSFWorkbook wb = new HSSFWorkbook();  
        HSSFSheet sheet = wb.createSheet("系统字典");  
        HSSFRow row = sheet.createRow((int) 0);  
        HSSFCellStyle style = wb.createCellStyle();  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
        
        HSSFCell cell = null;
        String[] headers = {"字典分类编码","字典分类名称","字典编码","字典名称","备注","更新人","更新时间","状态"};
        for(int i = 0;i<headers.length;i++){
        	 cell = row.createCell(i);
             cell.setCellValue(headers[i]);  
             cell.setCellStyle(style);  
        }
        
        List<Object> list = getFormNoPage(page);
        for (int i = 0; i < list.size(); i++)  
        {  
            row = sheet.createRow((int) i + 1);  
            Object[] obj = (Object[]) list.get(i);  
            for(int j = 0;j<obj.length;j++){
            	row.createCell(j).setCellValue(obj[j]==null?"":obj[j].toString());  
            }
            
        }  
        try {   
            //OutputStream os = new FileOutputStream("D:/sysDic.xls");  
            wb.write(os);  
            os.close();  
            page.setSuccess(true);
        } catch (Exception e){  
           page.setSuccess(false);
            e.printStackTrace();  
        } 
       
	}
	
	@SuppressWarnings("unchecked")
	private List<Object> getFormNoPage(Pagination page){
		//String sysDicId = page.getConditions().get("sys_dic_id");
		String classifyCode = page.getConditions().get("classifyCode");
		String classifyName = page.getConditions().get("classifyName");
		String dicCode = page.getConditions().get("dicCode");
		String dicName = page.getConditions().get("dicName");
		String remark = page.getConditions().get("remark");
		String status = page.getConditions().get("status");
		String updDatetime = page.getConditions().get("updDatetime");
		String updUser = page.getConditions().get("upduser");
		String sortId = page.getConditions().get("sortId");
		StringBuffer sql = new StringBuffer();
		sql.append("select s.classify_code classifyCode,s.classify_name classifyName, ");
		//sql.append("select s.sys_dic_id sysDicId ,s.classify_code classifyCode,s.classify_name classifyName, ");
		sql.append(" s.dic_code dicCode,s.dic_name dicName,s.remark remark, ");
		//sql.append(" u.name updUser,DATE_FORMAT(s.upd_datetime,'%Y-%m-%d %H:%i:%S') updDatetime,s.status status ");
		sql.append(" u.name updUser,DATE_FORMAT(s.upd_datetime,'%Y-%m-%d %H:%i:%S') updDatetime, case s.status when 0 then '停用' else '启用' end  status ");
		sql.append(" from dic_sysdic s left join sys_user u on s.upd_user=u.id where 1=1 ");
		
		//if(StringUtils.isNotBlank(sysDicId)){
			//sql.append(" and s.sys_dic_id = :sysDicId ");
		//}
		if(StringUtils.isNotBlank(classifyCode)){
			sql.append(" and s.classify_code = :classifyCode ");
		}
		if(StringUtils.isNotBlank(classifyName)){
			sql.append(" and s.classify_name like :classifyName ");
		}
		if(StringUtils.isNotBlank(dicCode)){
			sql.append(" and s.dic_code = :dicCode ");
		}
		if(StringUtils.isNotBlank(dicName)){
			sql.append(" and s.dic_name like :dicName ");
		}
		if(StringUtils.isNotBlank(remark)){
			sql.append(" and s.remark = :remark ");
		}
		if(StringUtils.isNotBlank(status)){
			sql.append(" and s.status = :status ");
		}
		if(StringUtils.isNotBlank(updDatetime)){
			sql.append(" and s.upd_datetime = :updDatetime ");
		}
		if(StringUtils.isNotBlank(updUser)){
			sql.append(" and s.upd_user = :updUser ");
		}
		if(StringUtils.isNotBlank(sortId)){
			sql.append(" and s.sort_id = :sort_id ");
		}
		sql.append("     order by s.classify_code asc,cast(s.dic_code as unsigned) asc ");
		Query query = getSession().createSQLQuery(sql.toString());
		//if(StringUtils.isNotBlank(sysDicId)){
			//query.setParameter("sys_dic_id",  sysDicId );
		//}
		if(StringUtils.isNotBlank(classifyCode)){
			query.setParameter("classifyCode",  classifyCode );
		}
		if(StringUtils.isNotBlank(classifyName)){
			query.setParameter("classifyName", "%"+ classifyName+"%" );
		}
		if(StringUtils.isNotBlank(dicCode)){
			query.setParameter("dicCode",  dicCode );
		}
		if(StringUtils.isNotBlank(dicName)){
			query.setParameter("dicName", "%"+ dicName+"%" );
		}
		if(StringUtils.isNotBlank(remark)){
			query.setParameter("remark",  remark );
		}
		if(StringUtils.isNotBlank(status)){
			query.setParameter("status",  status );
		}
		if(StringUtils.isNotBlank(updDatetime)){
			query.setParameter("updDatetime",  updDatetime );
		}
		if(StringUtils.isNotBlank(updUser)){
			query.setParameter("updUser",  updUser );
		}
		if(StringUtils.isNotBlank(sortId)){
			query.setParameter("sortId",  sortId );
		}
			return query.list();

	
	}
}
